using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;

namespace CodeGenerator.Classes
{
    public class GeneratorPages
    {
        private string mTableName = "";
        private string connStr = "";

        public static string NameSpace = string.Empty;

        public GeneratorPages(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }

        public static string PropCaseStartLower(string strText)
        {
            string retVal = PropCase(strText);
            retVal = retVal.Substring(0, 1).ToLower() + retVal.Substring(1, (retVal.Length - 1));
            return retVal;
        }

        public static string PropCase(string strText)
        {
            return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());
        }

        public string GeneratPages()
        {
           StringBuilder sql = new StringBuilder();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            string cmdTxt = "select * from  INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME ='"+mTableName+"'";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdTxt;
            cmd.Connection = conn;
            cmd.Dispose();

            SqlDataAdapter oda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            oda.Fill(dt);

            string TABLE_NAME = string.Empty;
            string tType = string.Empty;
            string pkTable = string.Empty;
            string keyType = string.Empty;
            string keyDbType= string.Empty;

            string collName = string.Empty;
            string destColl = string.Empty;
            string parColl = string.Empty;
            string dbType = string.Empty;
            string isNull = string.Empty;
            string updCollPar= string.Empty;
            string collTemp = string.Empty;
            string parTemp = string.Empty;

            string insColl = string.Empty;
            string insCollPar = string.Empty;

            string nameColl = string.Empty;


            foreach (DataRow dr in dt.Rows)
            {

                TABLE_NAME = PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "");

                pkTable = GetPK(dr["TABLE_NAME"].ToString());

                cmdTxt = "select data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '" + dr["TABLE_NAME"].ToString() + "' and ordinal_position = 1";

                SqlCommand cmdType = new SqlCommand();
                cmdType.CommandText = cmdTxt;
                cmdType.Connection = conn;

                SqlDataAdapter odaColl = new SqlDataAdapter(cmdType);
                DataTable dtColl = new DataTable();
                odaColl.Fill(dtColl);

                keyType = DecodeType(dtColl.Rows[0][0].ToString());
                keyDbType = DecodeTypeForDB(dtColl.Rows[0][0].ToString());

                cmdTxt = @"SELECT 
					TABLE_CATALOG,
					TABLE_SCHEMA, 
					TABLE_NAME, 
					COLUMN_NAME, 
					ORDINAL_POSITION, 
					COLUMN_DEFAULT AS DefaultSetting, 
					IS_NULLABLE,
					DATA_TYPE, 
					CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
					DATETIME_PRECISION AS DatePrecision,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
				FROM  INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME= '" + dr["TABLE_NAME"].ToString() + "' ORDER BY Ordinal_Position";


                SqlCommand cmdColl = new SqlCommand();
                cmdColl.CommandText = cmdTxt;
                cmdColl.Connection = conn;
                cmdColl.Dispose();

                odaColl = new SqlDataAdapter(cmdColl);
                dtColl = new DataTable();
                odaColl.Fill(dtColl);

                collName = string.Empty;
                destColl = string.Empty;
                parColl = string.Empty;
                dbType = string.Empty;
                isNull = string.Empty;
                updCollPar = string.Empty;
                collTemp = string.Empty;
                parTemp = string.Empty;

                insColl = string.Empty;
                insCollPar = string.Empty;

                nameColl = string.Empty;

                foreach (DataRow drColl in dtColl.Rows)
                {
                    collTemp = drColl["COLUMN_NAME"].ToString();

                    insColl = insColl + ", " + "\n" +"'"+ collTemp+"'";

                    nameColl = nameColl + ", " + "\n" + "{ name:'" + PropCaseStartLower(collTemp).Replace("_", "") + "'" + " ,hidden:false, index:'" + collTemp + "', width:100}";
                }

                insColl = insColl.TrimStart(',').Trim();
                nameColl = nameColl.TrimStart(',').Trim();
                sql.AppendLine("<%@ Page Title=" + "\"" + "\"" + " Language=" + "\"" + "C#" + "\"" + " MasterPageFile=" + "\"" + "~/Site.Master" + "\"" + " AutoEventWireup=" + "\"" + "true" + "\"" + " CodeFile=" + "\"" + PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "")+"_Pages.cs"+  "\"" + " Inherits=" + "\"" +NameSpace+"."+ PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "") +"_Pages"+ "\"" + " %>");
                sql.AppendLine("\n");
                sql.AppendLine("<asp:Content ID=" + "\"" + "Content1" + "\"" + " ContentPlaceHolderID=" + "\"" + "HeadContent" + "\"" + " Runat=" + "\"" + "Server" + "\"" + ">");
                sql.AppendLine("<script type=" + "\"" + "text/javascript" + "\"" + " language=" + "\"" + "javascript" + "\"" + ">");
                sql.AppendLine("$(function () {");
                sql.AppendLine("\t\t$(" + "\"" + "#table" + "\"" + ").jqGrid({");
                sql.AppendLine("\t\turl: '../../Ajaxhandler/Administration/" + PropCaseStartLower(dr["TABLE_NAME"].ToString()) + ".ashx?method=getdata',");
                sql.AppendLine("\t\tdatatype: 'json',");
                sql.AppendLine("colNames: [" + (insColl) + "],");
                sql.AppendLine("colModel: [");
                sql.AppendLine(nameColl);
                sql.AppendLine("\t\t],");
                sql.AppendLine("\t\trowNum: 10,");
                sql.AppendLine("\t\tid: '" + GetPK(mTableName) + "',");
                sql.AppendLine("\t\tmultiselect: true,");
                sql.AppendLine("\t\trowList: [5, 10, 15, 20],");
                sql.AppendLine("\t\tpager: '#pager',");
                sql.AppendLine("\t\tsortname: '" + GetPK(mTableName) + "',");
                sql.AppendLine("\t\tsortorder: " + "\"" + "asc" + "\"" + ",");
                sql.AppendLine("\t\tviewrecords: true,");
                sql.AppendLine("\t\tjsonReader: { repeatitems: false },");
                sql.AppendLine("\t\twidth: 600,");
                sql.AppendLine("\t\theight: '100%',");
                sql.AppendLine("\t\tcaption: '" + PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "") + "',");
                sql.AppendLine("\t\tajaxGridOptions: { cache: false },");
                sql.AppendLine("\t\tondblClickRow: function () {");
                sql.AppendLine("\t\t\tOpenForm('edit');");
                sql.AppendLine("\t\t}");
                sql.AppendLine("\t\t});");
                sql.AppendLine("\n");
                sql.AppendLine("});");
                sql.AppendLine("\n");
                sql.AppendLine("\t\tfunction OpenForm(ctl) {");
                sql.AppendLine("\t\t\tvar grid = $(" + "\"" + "#table" + "\"" + ");");
                sql.AppendLine("\t\t\tvar selIds = grid.jqGrid('getGridParam', 'selarrrow'),");
                sql.AppendLine("\t\t\tselText = [];");
                sql.AppendLine("\t\t\t$.each(selIds, function () {");
                sql.AppendLine("\t\t\t\t\tselText.push(grid.jqGrid('getCell', this, '" + PropCaseStartLower(GetPK(mTableName)).Replace("_", "") + "'));");
                sql.AppendLine("\t\t\t});");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tif (ctl == " + "\"" + "edit" + "\"" + ") {");
                sql.AppendLine("\t\t\t\tif (selIds.length == 0) {");
                sql.AppendLine("\t\t\t\t}");
                sql.AppendLine("\t\t\t\telse if (selIds.length > 1) {");
                sql.AppendLine("\t\t\t\t\talert('Pilih satu record untuk ubah data.');");
                sql.AppendLine("\t\t\t\t}");
                sql.AppendLine("\t\t\telse {");
                sql.AppendLine("\t\t\t\t\t$(" + "\"" + "#dialog" + "\"" + ").load('../../Form/Administration/"+PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_","")+"_Form" + ".aspx?Id=' + selText).dialog({ modal: true, width: 400, height: 150, title: 'Edit Data' });");
                sql.AppendLine("\t\t\t\t}");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\t\telse if (ctl == " + "\"" + "add" + "\"" + ") {");
                sql.AppendLine("\t\t\t\t\t$(" + "\"" + "#dialog" + "\"" + ").load('../../Form/Administration/" + PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_","")+"_Form" + ".aspx').dialog({ modal: true, width: 400, height: 150, title: 'Add Data' });");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\t\telse {");
                sql.AppendLine("\t\t\tvar result = window.confirm('Are you sure that you want to delete?');");
                sql.AppendLine("\t\t\tif (result) {");
                sql.AppendLine("\t\t\t\tCallAjax(" + "\"" + "../../ajaxhandler/Administration/" + PropCaseStartLower(dr["TABLE_NAME"].ToString()) + ".ashx" + "\"" + ", " + "\"" + "?method=delete" + "\"" + "+" + "\"" + "&id=" + "\"" + " + selText);");
                sql.AppendLine("\t\t\t\tsetTimeout(" + "\"" + "$('#table').trigger('reloadGrid');" + "\"" + ", 3000);");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\t}");
                sql.AppendLine("\t}");
                sql.AppendLine("</script>");
                sql.AppendLine("\n");
                sql.AppendLine("</asp:Content>");
                sql.AppendLine("<asp:Content ID="+"\""+"Content2"+"\""+" ContentPlaceHolderID="+"\""+"MainContent"+"\""+" Runat="+"\""+"Server"+"\""+">");
                sql.AppendLine("<div>");
                sql.AppendLine("<a href="+"\""+"javascript:void(0);"+"\""+" id="+"\""+"dialog_link_add"+"\""+" class="+"\""+"dialog_link ui-state-default ui-corner-all"+"\""+" onclick="+"\""+"OpenForm('add')"+"\""+" title="+"\""+"Add"+"\""+"><span class="+"\""+"ui-icon ui-icon-plus"+"\""+"></span>Add</a>");
                sql.AppendLine("<a href=" + "\"" + "javascript:void(0);" + "\"" + " id=" + "\"" + "dialog_link_edit" + "\"" + " class=" + "\"" + "dialog_link ui-state-default ui-corner-all" + "\"" + " onclick=" + "\"" + "OpenForm('edit')" + "\"" + " title=" + "\"" + "Edit" + "\"" + "><span class=" + "\"" + "ui-icon ui-icon-plus" + "\"" + "></span>Edit</a>");
                sql.AppendLine("<a href=" + "\"" + "javascript:void(0);" + "\"" + " id=" + "\"" + "dialog_link_delete" + "\"" + " class=" + "\"" + "dialog_link ui-state-default ui-corner-all" + "\"" + " onclick=" + "\"" + "OpenForm('delete')" + "\"" + " title=" + "\"" + "Delete" + "\"" + "><span class=" + "\"" + "ui-icon ui-icon-plus" + "\"" + "></span>Delete</a>");
                sql.AppendLine("</div>");
                sql.AppendLine("<br />");
                sql.AppendLine("<table id="+"\""+"table"+"\""+" class="+"\""+"scroll"+"\""+" cellpadding="+"\""+"0"+"\""+" cellspacing="+"\""+"0"+"\""+"></table>");
                sql.AppendLine("<div id="+"\""+"pager"+"\""+" class="+"\""+"scroll"+"\""+"></div>");
                sql.AppendLine("<br />");
                sql.AppendLine("<div id="+"\""+"dialog"+"\""+" title="+"\""+"Default dialog"+"\""+"></div>");
                sql.AppendLine("</asp:Content>");

            }

            return sql.ToString();
        }

        public string GeneratPagesCode()
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("using System;");
            sql.AppendLine("using System.Collections.Generic;");
            sql.AppendLine("using System.Linq;");
            sql.AppendLine("using System.Web;");
            sql.AppendLine("using System.Web.UI;");
            sql.AppendLine("using System.Web.UI.WebControls;");
            sql.AppendLine("\n");
            sql.AppendLine("namespace " + NameSpace);
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tpublic partial class " + PropCaseStartLower(mTableName).Replace("_", "") + "_Pages" + ": BasePage");
            sql.AppendLine("\t\t{");
            sql.AppendLine("\t\t\tprotected void Page_Load(object sender, EventArgs e)");
            sql.AppendLine("\t\t\t{");
            sql.AppendLine("\n");
            sql.AppendLine("\t\t\t}");
            sql.AppendLine("\t\t}");
            sql.AppendLine("\t}");

            return sql.ToString();
        }

        private string GetPK(string table)
        {

            string pk = "";
            DataTable pkTable = new DataTable();
            string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";

            SqlCommand cmd = GetCommand(sql);
            cmd.Parameters.AddWithValue("@tableName", table);
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            if (result != null)
                pk = result.ToString();

            return pk;
        }

        private string DecodeType(string sqlType)
        {
            sqlType = sqlType.ToLower();
            string sysType = "string";
            switch (sqlType)
            {
                case "bigint":
                    sysType = "long";
                    break;
                case "smallint":
                    sysType = "short";
                    break;
                case "int":
                    sysType = "int";
                    break;
                case "uniqueidentifier":
                    sysType = "Guid";
                    break;
                case "smalldatetime":
                case "datetime":
                case "date":
                    sysType = "DateTime";
                    break;
                case "float":
                    sysType = "double";
                    break;
                case "real":
                case "numeric":
                case "smallmoney":
                case "decimal":
                case "money":
                    sysType = "decimal";
                    break;
                case "tinyint":
                    sysType = "byte";
                    break;
                case "bit":
                    sysType = "bool";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                case "timestamp":
                    sysType = "byte[]";
                    break;
            }
            return sysType;
        }
        private string DecodeTypeForDB(string sqlType)
        {
            switch (sqlType)
            {
                case "varchar":
                    return "VarChar";
                case "nvarchar":
                    return "NVarChar";
                case "int":
                    return "Int";
                case "uniqueidentifier":
                    return "UniqueIdentifier";
                case "datetime":
                    return "DateTime";
                case "bigint":
                    return "BigInt";
                case "binary":
                    return ".Binary";
                case "bit":
                    return "Bit";
                case "char":
                    return "Char";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Float";
                case "image":
                    return "Image";
                case "money":
                    return "Money";
                case "nchar":
                    return "NChar";
                case "ntext":
                    return "NText";
                case "numeric":
                    return "Decimal";
                case "real":
                    return "Real";
                case "smalldatetime":
                    return "SmallDateTime";
                case "smallint":
                    return "SmallInt";
                case "smallmoney":
                    return "SmallMoney";
                case "sql_variant":
                    return "Variant";
                case "sysname":
                    return "VarChar";
                case "text":
                    return "Text";
                case "timestamp":
                    return "Timestamp";
                case "tinyint":
                    return "TinyInt";
                case "varbinary":
                    return "VarBinary";
                case "xml":
                    return "Xml";
                default:
                    return "VarChar";
            }
        }

        private SqlCommand GetCommand(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd;
        }
    }
}
